/*package com.sxc.oa.web.action;

import com.sxc.oa.utils.DButil;
import jakarta.servlet.ServletException;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import static com.sxc.oa.utils.DButil.close;

public class DepDelServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        //根据部门编号，删除部门
        //获取部门编号
        String deptno = request.getParameter("deptno");
        Connection conn = null;
        PreparedStatement ps = null;

        int count;
        try {
            conn = DButil.getConnction();
            //开启事务（自动提交机制关闭）
            conn.setAutoCommit(false);
            String sql = "delete from dept where deptno = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, deptno);
            //返回值是：影响了数据库表中多少条记录。
            count = ps.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                close(conn, ps, null);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        //判断删除成功了还是失败了
        if (count == 1) {
            //删除成功
            // 仍然跳转到部门列表页面
            //部门列表页面的显示需要执行另一个Servlet。怎么办，转发
            request.getRequestDispatcher("/dept/list").forward(request,response);
        }else{
            //删除失败
            request.getRequestDispatcher("/error.html").forward(request,response);
        }


    }
}
*/
/*package com.sxc.oa.web.action;

import com.sxc.oa.utils.DButil;
import jakarta.servlet.ServletException;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

import static com.sxc.oa.utils.DButil.close;

public class DepDelServlet extends HttpServlet {
    private static final Logger LOGGER = Logger.getLogger(DepDelServlet.class.getName());

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        //根据部门编号，删除部门
        //获取部门编号
        String deptno = request.getParameter("deptno");
        Connection conn = null;
        PreparedStatement ps = null;

        int count = 0;
        try {
            conn = DButil.getConnction();
            //开启事务（自动提交机制关闭）
            conn.setAutoCommit(false);
            String sql = "delete from dept where deptno = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, deptno);
            //返回值是：影响了数据库表中多少条记录。
            count = ps.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "SQL Exception occurred", e);
            // 回滚事务
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    LOGGER.log(Level.SEVERE, "Failed to rollback transaction", ex);
                }
            }
            throw new ServletException("Failed to delete department", e);
        } finally {
            try {
                close(conn, ps, null);
            } catch (SQLException e) {
                LOGGER.log(Level.SEVERE, "Failed to close resources", e);
            }
        }
        //判断删除成功了还是失败了
        //转发
        if (count == 1) {
            //删除成功
            // 仍然跳转到部门列表页面
            //部门列表页面的显示需要执行另一个Servlet。怎么办，转发
            request.getRequestDispatcher("/dept/list").forward(request, response);
        } else {
            //删除失败
            request.getRequestDispatcher("/error.html").forward(request, response);
        }
    }
}*/
package com.sxc.oa.web.action;

import com.sxc.oa.utils.DButil;
import jakarta.servlet.ServletException;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

import static com.sxc.oa.utils.DButil.close;

public class DepDelServlet extends HttpServlet {
    private static final Logger LOGGER = Logger.getLogger(DepDelServlet.class.getName());

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        //根据部门编号，删除部门
        //获取部门编号
        String deptno = request.getParameter("deptno");
        Connection conn = null;
        PreparedStatement psCheck = null;
        PreparedStatement psDelete = null;
        ResultSet rs = null;

        int count = 0;
        try {
            conn = DButil.getConnction();
            //开启事务（自动提交机制关闭）
            conn.setAutoCommit(false);

            // 检查是否有成员在使用这个部门
            String checkSql = "SELECT COUNT(*) FROM sss WHERE deptno1 = ?";
            psCheck = conn.prepareStatement(checkSql);
            psCheck.setString(1, deptno);
            rs = psCheck.executeQuery();

            if (rs.next() && rs.getInt(1) > 0) {
                // 有成员在使用这个部门，不能删除
                request.setAttribute("errorMsg", "无法删除，该部门下还有成员存在！");
                request.getRequestDispatcher("/error.jsp").forward(request, response);
                return;
            }

            // 没有成员使用这个部门，可以删除
            String deleteSql = "DELETE FROM dept WHERE deptno = ?";
            psDelete = conn.prepareStatement(deleteSql);
            psDelete.setString(1, deptno);
            count = psDelete.executeUpdate();

            conn.commit();
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "SQL Exception occurred", e);
            // 回滚事务
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    LOGGER.log(Level.SEVERE, "Failed to rollback transaction", ex);
                }
            }
            throw new ServletException("Failed to delete department", e);
        } finally {
            try {
                close(conn, psCheck, rs);
                close(null, psDelete, null);
            } catch (SQLException e) {
                LOGGER.log(Level.SEVERE, "Failed to close resources", e);
            }
        }

        //判断删除成功了还是失败了
        if (count == 1) {
            //删除成功
            request.getRequestDispatcher("/dept/list").forward(request, response);
        } else {
            //删除失败
            request.getRequestDispatcher("/error.jsp").forward(request, response);
        }
    }
}